﻿'------AUTHOR-----------------------DATE--------VERSION-----------------------NOTES----------------------------
'    Engin AYDOGDU, Samet SAYGILI ' 28.05.2014 '    1.0    '     INITIAL CREATION FOR MENU HYBRID TARIFF PROCESS
'------------------------------------------------------------------------------------------------
Imports ccapps.persistent.Telnet
Imports ccapps.persistent.Telnet.Scripting
Imports ccapps.persistent.FTP
Imports System.Globalization
Imports System.Threading
Imports ccapps.persistent.MenuAssigner

Public Class MenuHybridTariffProcess
    Inherits [lib].BasePage

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Me.PageTitle = "Menu Hybrid Tariff Process - Step1"
        Session("_MenuAssignerDetailRows") = Nothing

        If Not Page.IsPostBack Then
            Lblok.Text = ""
        End If

    End Sub

    Private Sub btnCheckCoID_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCheckCoID.Click, btnCheckMsisdn.Click, btnCheckCustCode.Click
        Dim coID As Integer = 0
        Dim customerPointer As String = "CoID"

        If sender Is Me.btnCheckCoID Then
            If Me.txtCoID.Text.Trim().Length = 0 OrElse Not IsNumeric(Me.txtCoID.Text.Trim()) Then
                Alert("Lütfen uygun formatta bir CoID belirtin!", Me)
                Return
            End If
            coID = CInt(Me.txtCoID.Text.Trim())
        ElseIf sender Is Me.btnCheckMsisdn Then
            If Me.txtMsisdn.Text.Trim().Length = 0 OrElse Not IsNumeric(Me.txtMsisdn.Text.Trim()) Then
                Alert("Lütfen uygun formatta bir MSISDN belirtin!", Me)
                Return
            ElseIf IsDBNull(persistent.myDBConn.ORAScalar("select fn_msisdn_to_coid(" & Me.txtMsisdn.Text.Trim() & ") from dual")) Then
                Alert("Abone bulunamadi!", Me)
                Return
            End If
            coID = persistent.myDBConn.ORAScalar("select fn_msisdn_to_coid(" & Me.txtMsisdn.Text.Trim() & ") from dual")
            customerPointer = "Msisdn"
        ElseIf sender Is Me.btnCheckCustCode Then
            If Me.txtCustCode.Text.Trim().Length = 0 Then
                Alert("Lütfen uygun formatta bir CustCode belirtin!", Me)
                Return
            End If
            Dim contractsCount As Integer = persistent.myDBConn.ORAScalar("select count(distinct coa.co_id) from customer_all cua, contract_all coa where coa.customer_id = cua.customer_id and  (cua.custcode = '" & Me.txtCustCode.Text.Trim() & "' or cua.custcode like '" & Me.txtCustCode.Text.Trim() & ".%')")
            If contractsCount = 0 Then
                Alert("Abone bulunamadi!", Me)
                Return
            ElseIf contractsCount > 1 Then
                Alert("Girdiginiz CustCode tek bir abone içermelidir!", Me)
                Return
            Else
                coID = persistent.myDBConn.ORAScalar("select distinct coa.co_id from customer_all cua, contract_all coa where coa.customer_id = cua.customer_id and  (cua.custcode = '" & Me.txtCustCode.Text.Trim() & "' or cua.custcode like '" & Me.txtCustCode.Text.Trim() & ".%')")
                customerPointer = "CustCode"
            End If
        End If

        Dim coIDexist As Integer
        'LOGLAMA
        coIDexist = persistent.myDBConn.ORAScalar_LOG("select count(co_id) from contract_all where co_id = " & coID, _
                                                      "", "", coID, "", "SELECT(Coid)", "MenuHybridTariffProcess", Session("user"))

        If coIDexist = 0 Then
            Alert("Abone bulunamadi!", Me)
            Return
        End If

        Dim coExists As Integer = persistent.myDBConn.ORAScalar("select count(distinct co_id) from contract_all where co_id = " & coID)

        If coExists = 0 Then
            Alert("Abone bulunamadi!", Me)
            Me.lblCurrentTariff.Text = ""
            Return
        End If

        Me.CoID = coID

        Dim custCode As String = persistent.myDBConn.ORAScalar("select custcode from customer_all where customer_id = (select customer_id from contract_all where co_id = " & coID & ")")
        Dim customerId As String = persistent.myDBConn.ORAScalar("select customer_id from contract_all where co_id = " & coID)

        Dim dtCustomer As DataTable = persistent.TariffChange.GetCustomer(custCode)

        If (dtCustomer.Rows(0)("cstype").ToString().ToUpper() <> "A") Then
            Alert("Abone aktif degil!", Me)
            Return
        End If

        Dim coStatus As Char = persistent.myDBConn.ORAScalar("select ch_status from contract_all where co_id = " & coID & "")
        Dim curCulture As CultureInfo = Thread.CurrentThread.CurrentCulture
        Dim tInfo As TextInfo = curCulture.TextInfo()
        Trim(tInfo.ToLower(coStatus))

        If coStatus <> "a" Then
            Alert("Contract Aktif Degil!", Me)
            Me.lblCurrentTariff.Text = ""
            Return
        End If

        'Abone Kontrollu Esnaf Tarifesinde mi Kontrolü ...
        Dim vnExist As Integer
        Dim vnResult As Integer = checkTariff(vnExist, Me.CoID)
        If vnResult = 0 Then
            Alert("Abone Kontrollu Esnaf Tarifesinde Degil! Bu ekrandan sadece Kontrollu Esnaf Tarifesindeki aboneler goruntulenebilir.", Me)
            Me.lblCurrentTariff.Text = ""
            Return
        End If

        Dim checkSmsPckgs As Integer = persistent.myDBConn.ORAScalar("SELECT count(distinct ps.sncode) FROM profile_service ps, pr_serv_status_hist pss where ps.co_id = pss.co_id and ps.sncode = pss.sncode and ps.status_histno = pss.histno and ps.profile_id = pss.profile_id and upper(pss.status) = 'A' and ps.co_id = " & coID & " and ps.sncode in (select distinct sncode from eu_bill.menu_tariff_configuration mtc where upper(mtc.package_type) = 'SMS')")
        Dim checkDataPckgs As Integer = persistent.myDBConn.ORAScalar("SELECT count(distinct ps.sncode) FROM profile_service ps, pr_serv_status_hist pss where ps.co_id = pss.co_id and ps.sncode = pss.sncode and ps.status_histno = pss.histno and ps.profile_id = pss.profile_id and upper(pss.status) = 'A' and ps.co_id = " & coID & " and ps.sncode in (select distinct sncode from eu_bill.menu_tariff_configuration mtc where upper(mtc.package_type) = 'DATA')")
        Dim checkCmpgnPckgs As Integer = persistent.myDBConn.ORAScalar("SELECT count(distinct ps.sncode) FROM profile_service ps, pr_serv_status_hist pss where ps.co_id = pss.co_id and ps.sncode = pss.sncode and ps.status_histno = pss.histno and ps.profile_id = pss.profile_id and upper(pss.status) = 'A' and ps.co_id = " & coID & " and ps.sncode in (select distinct sncode from eu_bill.menu_tariff_configuration mtc where upper(mtc.package_type) = 'CAMPAIGN')")
        Dim checkAddCmpgnPckgs As Integer = persistent.myDBConn.ORAScalar("SELECT count(distinct ps.sncode) FROM profile_service ps, pr_serv_status_hist pss where ps.co_id = pss.co_id and ps.sncode = pss.sncode and ps.status_histno = pss.histno and ps.profile_id = pss.profile_id and upper(pss.status) = 'A' and ps.co_id = " & coID & " and ps.sncode in (select distinct sncode from eu_bill.menu_tariff_configuration mtc where upper(mtc.package_type) = 'ADD_CAMPAIGN')")


        'Setting Sms Package Text
        If checkSmsPckgs = 0 Then
            lblSmsPackage.Text = "Abonenin Uzerinde Menu Tarife Sms Paketi Yoktur."
        ElseIf checkSmsPckgs = 1 Then
            lblSmsPackage.Text = persistent.myDBConn.ORAScalar("SELECT des FROM mpusntab mps where sncode in (select ps.sncode from profile_service ps, pr_serv_status_hist pss where ps.co_id = pss.co_id and ps.sncode = pss.sncode and ps.status_histno = pss.histno and ps.profile_id = pss.profile_id and upper(pss.status) = 'A' and ps.co_id = " & coID & " and ps.sncode in (select distinct sncode from eu_bill.menu_tariff_configuration mtc where upper(mtc.package_type) = 'SMS'))")
        Else
            Alert("Abonenin Uzerinde Birden Cok Menu Tarife Sms Paketi Bulunmaktadir..", Me)
            lblSmsPackage.Text = "Musteri Uzerinde Birden Cok Menu Tarife Sms Paketi Bulunmaktadir."
            'Return
        End If

        'Setting Data Package Text
        If checkDataPckgs = 0 Then
            lblDataPackage.Text = "Abonenin Uzerinde Menu Tarife Data Paketi Yoktur."
        ElseIf checkDataPckgs = 1 Then
            lblDataPackage.Text = persistent.myDBConn.ORAScalar("SELECT des FROM mpusntab mps where sncode in (select ps.sncode from profile_service ps, pr_serv_status_hist pss where ps.co_id = pss.co_id and ps.sncode = pss.sncode and ps.status_histno = pss.histno and ps.profile_id = pss.profile_id and upper(pss.status) = 'A' and ps.co_id = " & coID & " and ps.sncode in (select distinct sncode from eu_bill.menu_tariff_configuration mtc where upper(mtc.package_type) = 'DATA'))")
        Else
            Alert("Abonenin Uzerinde Birden Cok Menu Tarife Data Paketi Bulunmaktadir..", Me)
            lblDataPackage.Text = "Musteri Uzerinde Birden Cok Menu Tarife Data Paketi Bulunmaktadir."
            'Return
        End If

        'Setting Campaign Text
        If checkCmpgnPckgs = 0 Then
            lblCmpgn.Text = "Abonenin Uzerinde Menu Tarife Kampanyasi Yoktur."
        ElseIf checkCmpgnPckgs = 1 Then
            lblCmpgn.Text = persistent.myDBConn.ORAScalar("SELECT des FROM mpusntab mps where sncode in (select ps.sncode from profile_service ps, pr_serv_status_hist pss where ps.co_id = pss.co_id and ps.sncode = pss.sncode and ps.status_histno = pss.histno and ps.profile_id = pss.profile_id and upper(pss.status) = 'A' and ps.co_id = " & coID & " and ps.sncode in (select distinct sncode from eu_bill.menu_tariff_configuration mtc where upper(mtc.package_type) = 'CAMPAIGN'))")
        Else
            lblCmpgn.Text = ""
        End If

        'Setting Add Campaign Text
        If checkAddCmpgnPckgs = 0 Then
            lblAddCmpgn.Text = "Abonenin Uzerinde Ek Hat Kampanyasi Yoktur."
        ElseIf checkAddCmpgnPckgs = 1 Then
            lblAddCmpgn.Text = persistent.myDBConn.ORAScalar("SELECT des FROM mpusntab mps where sncode in (select ps.sncode from profile_service ps, pr_serv_status_hist pss where ps.co_id = pss.co_id and ps.sncode = pss.sncode and ps.status_histno = pss.histno and ps.profile_id = pss.profile_id and upper(pss.status) = 'A' and ps.co_id = " & coID & " and ps.sncode in (select distinct sncode from eu_bill.menu_tariff_configuration mtc where upper(mtc.package_type) = 'ADD_CAMPAIGN'))")
        Else
            Alert("Musteri Uzerinde Hatali Sayida Ek Hat Kampanyasi Bulunmaktadir!", Me)
            lblAddCmpgn.Text = "Musteri Uzerinde Hatali Sayida Ek Hat Kampanyasi Bulunmaktadir!"
        End If

        Me.lblCustCodeAndMsisdn.Text = custCode & " / " & persistent.myDBConn.ORAScalar("select fn_coid_to_msisdn(" & coID & ") from dual")
        Me.lblCustomerName.Text = persistent.common.GetNameOfCompany(custCode).Rows(0)("ccname")
        Me.lblCurrentTariff.Text = persistent.myDBConn.ORAScalar("SELECT des FROM mputmtab tm where tmcode = (select tmcode from contract_all where co_id = " & coID & ") AND vscode = (select max(vscode) from mputmtab tm2 where tm2.tmcode = tm.tmcode)")


        RegisterScript("var _CustomerPointer = '" & customerPointer & "';", Me)


        Me.Lblok.Text = ""

        Dim isTaxNoExist As Integer = persistent.myDBConn.ORAScalar("SELECT count(*) FROM ccontact_all WHERE cscomptaxno is NOT NULL and customer_id = (Select customer_id from contract_all where co_id =" & coID & " )")

        If isTaxNoExist > 0 Then
            Dim cscompTaxNo As String = persistent.myDBConn.ORAScalar("SELECT cscomptaxno FROM ccontact_all WHERE ccbill = 'X' and customer_id = " & customerId)
            Dim relatedCustomers As DataTable = persistent.myDBConn.myORATable("SELECT cca.cscompTaxNo,cca.customer_id,co.co_id,fn_coid_to_msisdn(co.co_id) msisdn,decode(co.ch_Status,'a','Aktif','s','Suspend','d','Deaktif') ch_status ,r.des tmdes FROM ccontact_all cca, contract_all co, rateplan r WHERE cca.cscomptaxno = '" & cscompTaxNo & "' AND cca.customer_id = co.customer_id AND cca.ccbill = 'X' AND co.tmcode = r.tmcode")
            Me.DataGrid1.Visible = True
            Me.DataGrid1.DataSource = relatedCustomers
            Me.DataGrid1.DataBind()
        Else
            Me.Lblok.Text = "Verilen abonenin Vergi Kimlik Numarası sistemde olmadığı için bilgilerine ulaşılamamaktadır."
            Me.DataGrid1.Visible = False
        End If

    End Sub

    Private Property CoID() As String
        Get
            Return Me.ViewState("__COID")
        End Get
        Set(ByVal Value As String)
            Me.ViewState("__COID") = Value
        End Set
    End Property

    Private Function checkTariff(ByVal vnResult As Integer, ByVal conID As Integer) As Integer
        'LOGLAMA
        Dim tmcode As Integer = persistent.myDBConn.ORAScalar_LOG("SELECT tmcode FROM mputmtab tm where tmcode = (select tmcode from contract_all where co_id = " & conID & ") AND vscode = (select max(vscode) from mputmtab tm2 where tm2.tmcode = tm.tmcode)", _
                                                                  "", "", conID, "", "SELECT(Tmcode)", "MenuAssignerSingleStep1", Session("user"))
        Dim tmCodeExist As Integer = persistent.myDBConn.ORAScalar("SELECT count(distinct tmcode) FROM eu_bill.esnaf_reference_tmcode WHERE tmtype = 'H' and tmcode = " & tmcode & "")
        If tmCodeExist > 0 Then
            vnResult = 1
        Else
            vnResult = 0
        End If
        Return vnResult
    End Function

    Private Sub DataGrid1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemDataBound

    End Sub

End Class